package com.ftg.learn.jdbc.chapter01;

import com.ftg.learn.chapter13.newmodel.Entry;
import com.ftg.learn.jdbc.chapter01.entity.Dept;
import com.ftg.learn.jdbc.chapter01.entity.Emp;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

/**
 * @author kangjx
 * @createTime 2022/9/22
 */
public class QueryUtil {

    private static String username = "root";
    private static String password = "Love0913@kjx";
    private static String url = "jdbc:mysql://localhost:3306/learn07?serverTimezone=UTC";

    /**
     * 返回值
     * 预编译sql
     *  select eno,ename,job,mgr from emp where eno = ?
     * @param sql
     */
    @SuppressWarnings("all")
    public static List<Map<String,Object>> queryForList(String sql, Object... para){
//        Map<String,Object> m = new HashMap<>();
//        m.values()
//        Set<String> sets = m.keySet();
//        Set<Map.Entry<String,Object>> sets = m.entrySet();
        List<Map<String,Object>> list = new ArrayList<>();
        try(
                Connection conn = DriverManager.getConnection(url, username, password);
                PreparedStatement stmt =  conn.prepareStatement(sql);
        ) {
            //就是为预编译sql 赋值
            if(Objects.nonNull(para)){
                for (int i = 0; i < para.length; i++) {
                    stmt.setObject(i + 1 ,para[i]);
                }
            }
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String,Object> map = new HashMap<>(5);
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT),rs.getObject(i+1));
                }
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 建议
     * @param sql
     * @param para
     * @return
     */
    public static List<Dept> queryForDept(String sql,Object...para){
        List<Dept> depts = new ArrayList<>(10);
        try(
                Connection conn = DriverManager.getConnection(url, username, password);
                PreparedStatement stmt =  conn.prepareStatement(sql);
        ) {
            //就是为预编译sql 赋值
            if(Objects.nonNull(para)){
                for (int i = 0; i < para.length; i++) {
                    stmt.setObject(i + 1 ,para[i]);
                }
            }
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Dept d = new Dept();
//                d.setDeptno(rs.getInt(1));
                d.setDname(rs.getString(2));
                d.setLoc(rs.getString(3));
                depts.add(d);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return depts;
    }

    /**
     * 通过传递class来完成动态类型转换
     *  清除转换
     *  类型转换
     * @param c    反射类 类名.class
     * @param sql  语句
     * @param para 参数
     * @return    List<Object>  Object 必须是一个表里面的字段与一个java类映射出来
     */
    public static List<Object> queryForClass(Class c,String sql,Object...para){
        if(Objects.nonNull(c)){
            List<Object> objs = new ArrayList<>(10);
            try(
                    Connection conn = DriverManager.getConnection(url, username, password);
                    PreparedStatement stmt =  conn.prepareStatement(sql);
            ) {
                //就是为预编译sql 赋值
                if(Objects.nonNull(para)){
                    for (int i = 0; i < para.length; i++) {
                        stmt.setObject(i + 1 ,para[i]);
                    }
                }
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                while (rs.next()) {
                    Object obj = null;
                    try {
                        obj = c.newInstance();
                        for (int i = 0; i < rsmd.getColumnCount(); i++) {
                            //大小写问题
                            String columnName = rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT);
                            PropertyDescriptor pd = new PropertyDescriptor(columnName,c);
                            Method writeMethod = pd.getWriteMethod();
                            String columnType = rsmd.getColumnTypeName(i + 1);
                            //转换类型处理
                            switch (columnType){
                                case "INT":
                                    writeMethod.invoke(obj,rs.getInt(i+1));
                                    break;
                                case "VARCHAR":
                                    writeMethod.invoke(obj,rs.getString(i+1));
                                    break;
//                                .....
                                default:
                                    writeMethod.invoke(obj,rs.getObject(i+1));
                            }
                        }

                    } catch (InstantiationException | IllegalAccessException | IntrospectionException | InvocationTargetException e) {
                        e.printStackTrace();
                    }
                    objs.add(obj);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return objs;
        }
        return null;
    }

    public static void main(String[] args) {
        List<Dept> depts = queryForDept("select deptno,dname,loc from dept");
        for (int i = 0; i < depts.size(); i++) {
            Dept d = depts.get(i);
//            System.out.println(d.getDeptno());
        }

        System.out.println("---无耻分隔线-----");
        List<Object> objs = queryForClass(Dept.class,"select deptno,dname,loc from dept");
        for (int i = 0; i < objs.size(); i++) {
            Dept d = (Dept)objs.get(i);
//            System.out.print(d.getDeptno() +"\t");
            System.out.println(d.getDname());
        }

        System.out.println("---无耻分隔线-----");
        List<Object> emps = queryForClass(Emp.class,"select * from emp");
        for (int i = 0; i < emps.size(); i++) {
            Emp emp = (Emp)emps.get(i);
            System.out.print("部门编号:" + emp.getDeptno() +"\t");
            System.out.print("职务:" + emp.getJob() +"\t");
            System.out.print("日期:" + emp.getHirdate() +"\t");
            System.out.print("工资:" + emp.getSal() +"\t");
            System.out.println("姓名:" + emp.getEname());
        }
    }

}
